# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

# 写DB
import pymysql
import datetime


class PetSpiderPipeline(object):

    def process_item(self, item, spider):
        # 创建DB链接
        db_url = "sh-cdb-5gzvy44k.sql.tencentcdb.com"
        db = pymysql.connect(host=db_url, user="root", passwd="Mollybox@2018",
                             db="test_mollybox", port=62798)

        # 创建cursor对象
        cursor = db.cursor()

        shop = item['shop']
        city = item['city']
        url = item['url']
        comments_count = item['comments_count']
        avg_consumption = item['avg_consumption']
        addr = item['addr']
        ct = datetime.datetime.now()
        ut = datetime.datetime.now()
        sql = 'insert into spider_pet_shop ' \
              '(shop, comments_count, avg_consumption, url, addr, city, create_time, update_time)' \
              'values (%s,%s,%s,%s,%s,%s,%s,%s)'

        # 使用execute()方法执行SQL语句
        cursor.execute(sql, (shop, comments_count, avg_consumption, url, addr, city, ct, ut))

        db.commit()
        db.close()
        cursor.close()

        fp = open('/Users/wienbc/Work/Python3.7/petspider/petspider/pet.txt', 'a+')
        fp.write(item['city'] + '\t')
        fp.write(item['shop'] + '\t')
        fp.write(item['url'] + '\t')
        fp.write(item['comments_count'] + '\t')
        fp.write(item['avg_consumption'] + '\t')
        fp.write(item['addr'] + 't')
        fp.write('\t\n')
        return item
